﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;

public partial class _35newpart_Linq_Linq查询语法Select_Where_OrderBy_OrderByDescending_GroupBy_Join_GroupJoin : System.Web.UI.Page
{
    LambdaExpression Lambdaexpression = new LambdaExpression();
    protected void Page_Load(object sender, EventArgs e)
    {
        Lambdaexpression._Summary_Select();

        Lambdaexpression._Summary_Where();

        Lambdaexpression._Summary_OrderBy();

       Lambdaexpression. _Summary_GroupBy();

       Lambdaexpression._Summary_Join();

       Lambdaexpression._Summary_JoinGroupBy();
    }


}



/// <summary>
///lambda Expression 数据得到
/// </summary>
public class LambdaExpression
{
    Linq查询DataContext DLC = new Linq查询DataContext();

    //Select - Select选择；延迟


    /// <summary>
    /// 运行程序
    /// </summary>
    public static void Run()
    {
        string[] _ary = null;
        _ary = new string[]{"asp.net", "csharp", "xhtml", "css", "javascript", 
            "wcf", "wpf", "silverlight", "linq", "wf", 
            "sqlserver", "asp.net ajax", "ssis", "ssas", "ssrs" };


        
    }
    /**/
    /// <summary>
    /// Select - Select选择；延迟
    /// </summary>
    public void _Summary_Select()
    {
        //// 使用Select查询操作符
        var sel = DLC.books.Select(ps=>new {p="dfsfs"+ps.bookid});

        System.Web.HttpContext.Current.Response.Write("使用Select查询操作符<br/>");
        foreach (var oo in sel)
        {
          System.Web.HttpContext.Current.Response.Write(oo.p);
        }

        ///与上面对应的查询语法
        var sel2 = from ps in DLC.books
                   select new
                   {
                       p = "类别名" + ps.bookid
                   };

        foreach (var oo in sel2)
        {
            System.Web.HttpContext.Current.Response.Write(oo.p);
        }
        
    }

    /// <summary>
    /// Where - Where查询；延迟
    /// </summary>
    public void _Summary_Where()
    {
        var ary = DLC.books.Where(ps =>
                ps.bookname.StartsWith("爱") || ps.bookname.EndsWith("1")
            );
           System.Web.HttpContext.Current.Response.Write(" Where - Where查询；延迟<br/>");
        foreach (var oo in ary)
        {
            System.Web.HttpContext.Current.Response.Write(oo.bookname);
        }

        var ary2 = from ps in DLC.books
                   where ps.bookname.StartsWith("爱")
                   ||
                   ps.bookname.EndsWith("1")

                   select ps;

        foreach (var oo in ary2)
        {
            System.Web.HttpContext.Current.Response.Write(oo.bookname);
        }


    }

    /// <summary>
    /// OrderBy - 按指定表达式对集合正序排序；延迟
    /// OrderByDescending - 按指定表达式对集合倒序排序；延迟
    /// </summary>
    public void _Summary_OrderBy()
    { 
        // 使用OrderBy查询操作符
        var ary = (from ps in DLC.books select ps).OrderBy(ps => ps.bookid).OrderByDescending(ps=>ps.bookname); //order by Descending 与 OrderBy 用法相同

        System.Web.HttpContext.Current.Response.Write(" 使用OrderBy查询操作符<br/>");
        foreach (var oo in ary)
        {
            System.Web.HttpContext.Current.Response.Write(oo.bookname);
        }


        // 与上面相同的查询语法
        var ary2 = from ps in DLC.books
                   orderby ps.adddate descending

                   select ps;

        System.Web.HttpContext.Current.Response.Write(" 使用OrderBy查询操作符<br/>");
        foreach (var oo in ary2)
        {
            System.Web.HttpContext.Current.Response.Write(oo.bookname);
        }


    
    }

    //GroupBy - 分组；延迟
    public void _Summary_GroupBy()
    {

        ///sql Group By 用法
        ///select [CX_loginState_Name], sum(CX_loginState_id) from CX_loginState group by [CX_loginState_name]
        // 使用GroupBy查询操作符
        var ary = (from ps in DLC.books select ps)
            .GroupBy(ps => ps.shjianame).Select(e => new { Group = e.Key, Member = e });
        foreach (var g in ary)
        {
            System.Web.HttpContext.Current.Response.Write(g.Group + "个字符：<br />");

            foreach (var s in g.Member)
            {
                System.Web.HttpContext.Current.Response.Write("--" + s.tp + "<br />");
            }
        }
        ///与上面相同的查询语法
        var arr2 = from ps in DLC.books
                   group ps by ps.shjianame
                       into e
                       select new
                           {
                               Group = e.Key,
                               Member = e
                           };

        foreach (var g in ary)
        {
            System.Web.HttpContext.Current.Response.Write(g.Group + "个字符：<br />");

            foreach (var s in g.Member)
            {
                System.Web.HttpContext.Current.Response.Write("--" + s.tp + "<br />");
            }
        }
        //



    }


    /// <summary>
    /// Join - Join查询；延迟
    /// </summary>
    public void _Summary_Join()
    {
        Linq_SQLServeDataContext LSqlDb = new Linq_SQLServeDataContext();

        var products = LSqlDb.M_Asks.Join
        (
        LSqlDb.M_AskClasses,
       n => n.ClassId,
       p => p.ClaId,
           (n, p) => new { n.Content, p.ClaName }
        ).Take(5);
        // 与上面的GroupBy查询操作符相对应的查询语法

        foreach (var oo in products)
        {
            System.Web.HttpContext.Current.Response.Write(oo.Content + "<br/>");
        }
        ///使用 join 联接
        ///from p in xx
        ///join s in other tab
        ///on p.Coulmn equals s.Coulmn
        ///select new {};
        var oup = (from n in LSqlDb.M_AskClasses
                   join p in LSqlDb.M_Asks
                   on n.ClaId equals p.ClassId
                   select new { p.Content, n.ClaName }).Take(5);


        foreach (var oo in oup.Take(200))
        {
            System.Web.HttpContext.Current.Response.Write(oo.ClaName + "<br/>");
        }
                         

                        


    }

    /// <summary>
    /// how to use Join group By 
    /// GroupJoin - 分组Join查询；延迟
    /// </summary>
    public void _Summary_JoinGroupBy()
    { 
                Linq_SQLServeDataContext LSqlDb = new Linq_SQLServeDataContext();

        // 使用GroupJoin查询操作符
                var products = LSqlDb.M_AskClasses
                               .GroupJoin(
                               LSqlDb.M_Asks,
                           c => c.ClaId,
                           p => p.ClassId,
                           (c, p) => new
                           {
                               c.ClaName,
                               products = p.Count()
                           }
                               );

                System.Web.HttpContext.Current.Response.Write("使用GroupJoin" + "<br/>");
                foreach (var oo in products.Take(200))
                {
                    System.Web.HttpContext.Current.Response.Write(oo.products + "<br/>");
                }

                var products2 = from p in LSqlDb.M_AskClasses
                                join s in LSqlDb.M_Asks
                                on p.ClaId equals s.ClassId
                                into g
                                select new
                                {
                                    CreateName = p.ClaName,
                                    products = g.Count()
                                };

                System.Web.HttpContext.Current.Response.Write("使用GroupJoin" + "<br/>");
                foreach (var oo in products2.Take(200))
                {
                    System.Web.HttpContext.Current.Response.Write(oo.products + "<br/>");
                }
    }

}